package com.bw.mapper;

import com.bw.bean.Vo;
import com.bw.bean.Vo1;
import com.bw.bean.Vo2;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Service;

import java.util.List;

@Mapper
public interface VoMapper {
    @Select("select\n" +
            "ci.chapter_name `name`,\n" +
            "count(u.id) `num`,\n" +
            "sum(u.position_sec) `time`,\n" +
            "count(distinct u.user_id) `count`,\n" +
            "sum(u.position_sec)/count(u.user_id) `avg`\n" +
            "from user_chapter_process u\n" +
            "left join chapter_info ci on u.chapter_id = ci.id\n" +
            "group by ci.chapter_name")
    List<Vo1> a();

    @Select("with a as\n" +
            "(select\n" +
            "    case\n" +
            "        when score>=0 and score<60 then '[0,60)'\n" +
            "        when score>=60 and score<80 then '[60,80)'\n" +
            "        when score>=80 and score<90 then '[80,90)'\n" +
            "        when score>=90 and score<95 then '[90,95)'\n" +
            "        when score>=95 and score<=100 then '[95,100]'\n" +
            "    end `name`\n" +
            "from test_exam)\n" +
            "select a.name,count(*) `value` from a group by a.name")
    List<Vo> b();

    @Select("select bp.name,sum(final_amount) `value` from order_info o\n" +
            "left join base_province bp on o.province_id = bp.id\n" +
            "group by bp.name")
    List<Vo> c();

    @Select("select sum(final_amount) from order_info")
    Double d();

    @Select("select hour(create_time) `name`,count(id) `value` from user_chapter_process group by hour(create_time) order by hour(create_time)")
    List<Vo> e();

    @Select("select bs.source_site `name`,\n" +
            "       sum(o.final_amount) `value`,\n" +
            "       count(distinct o.user_id) `num`,\n" +
            "       count(o.id) `count`\n" +
            "from order_info o\n" +
            "left join order_detail od on o.id = od.order_id\n" +
            "left join course_info ci on od.course_id = ci.id\n" +
            "left join chapter_info c on ci.id = c.course_id\n" +
            "left join video_info vi on c.id = vi.chapter_id\n" +
            "left join base_source bs on bs.id = vi.video_source_id\n" +
            "group by bs.source_site")
    List<Vo2> f();

    @Select("select count(*) from order_info where date(create_time)=date(now())")
    Double g();

    @Select("select ci.course_name `name`,sum(o.final_amount) `value` from order_info o\n" +
            "left join order_detail od on o.id = od.order_id\n" +
            "left join course_info ci on od.course_id = ci.id\n" +
            "group by ci.course_name")
    List<Vo> h();
}
